<?php

// /usr/local/bin/php -q /cron.newsletter.php 

require("public_html/classes/class.phpmailer.php");
require("public_html/functions/settings.php");
require("public_html/functions/buildLinks.php");

$i = 0;

$conn = mysql_connect( $dbHost, $dbUser, $dbPass ) or die("unable to connect to Database");
mysql_select_db($dbName);

$sql = "SELECT setting_value FROM settings WHERE setting_name = 'no_reply_email'";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
$settings=mysql_fetch_assoc($query);

$start_date = mktime(0,0,0,date("m"), date("n")-7, date("Y"));

	// max radius
	$sql = "SELECT radius FROM subscriptions ORDER BY radius DESC LIMIT 0,1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$rs=mysql_fetch_assoc($query);
	$highest_radius = $rs['radius'];

// all active/approved tradesmen
	
	$sql = "SELECT tradesman.id, company_name, email, latitude, longitude FROM tradesman 
			LEFT JOIN users ON tradesman.user_id = users.id 
			LEFT JOIN addresses ON tradesman.address_id = addresses.id
			WHERE active = 1 AND banned = 0 AND newsletter = 1";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	while($rs=mysql_fetch_assoc($query)){
	
	$trades_sql = array();
	
	// get trades
	$sql = "SELECT trade_id FROM tradesman_to_trade WHERE tradesman_id = '".$rs['id']."'";
	$trades_query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	while($trades_rs=mysql_fetch_assoc($trades_query)){
	$trades_sql[]="trade_id = '".$trades_rs['trade_id']."'";
	}
	
	
	// all jobs in last seven days within max radius
	
	$newsletter_content = '';

	$sql = "SELECT jobs.id, title, area, trade, ROUND(SQRT(POW((69.1 * (".$rs['latitude']." - addresses.latitude)), 2) + POW((53 * (".$rs['longitude']." - addresses.longitude)), 2)), 1) AS distance FROM jobs
			LEFT JOIN trades ON jobs.trade_id = trades.id 
			LEFT JOIN areas ON jobs.area_id = areas.id 
			LEFT JOIN addresses ON jobs.address_id = addresses.id 
			WHERE jobs.status = 'Open' AND job_active = '1' AND deleted != '1' AND date_added >= '".date("Y-m-d H:i:s", $start_date)."' AND date_added < NOW() AND (".implode(' OR ', $trades_sql).") 
			GROUP BY jobs.id 
			HAVING distance <= ".$highest_radius." 
			ORDER BY date_added";
	$jobs_query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$jobs_rows = mysql_num_rows($jobs_query);
	
	if($jobs_rows > 0){
	
	while($jobs_rs=mysql_fetch_assoc($jobs_query)){
	
	$link = build_job_link($jobs_rs['trade'],$jobs_rs['area'],$jobs_rs['id']);
	$newsletter_content.='<a href="http://www.iwantatradesman.co.uk'.$link .'" style="color: #002D4E; text-decoration: none;"><b>'.$jobs_rs['title'].'</b></a> - '.$jobs_rs['area'].'<br />';
	
	}

	$html_body = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> 
<html>
<head>
</head>
<body style="background-color: #eaeaea;">

	<table width="600" cellpadding="0" cellspacing="0" align="center" style="background-color: #eaeaea;">
	<tr><td valign="bottom" style="font-weight: bold; font-family: arial, sans-serif; font-size: 14px;"><a href="http://www.iwantatradesman.co.uk"><img src="http://www.iwantatradesman.co.uk/images/logo.gif" alt="I Want A Tradesman" border="0"></a>
	<br><br><a href="http://www.iwantatradesman.co.uk" style="color: #002D4E; text-decoration: none;">Home</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/post-a-job" style="color: #002D4E; text-decoration: none;">Post a job</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/tradesmen" style="color: #002D4E; text-decoration: none;">Tradesmen</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/view-jobs" style="color: #002D4E; text-decoration: none;">Jobs</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/help" style="color: #002D4E; text-decoration: none;">Help</a> &nbsp;|&nbsp; <a href="http://www.iwantatradesman.co.uk/blog" style="color: #002D4E; text-decoration: none;">Blog</a>
	</td></tr>
	<tr><td height="20"></td></tr>
	<tr><td style="color: #666666; font-size: 12px; font-family: arial, sans-serif;" align="left">Dear '.$rs['company_name'].'<br /><br />Welcome to this weeks job alert newsletter. Below you will find all jobs available to quote within a '.$highest_radius.' mile radius for your trades.<br /><br />
	'.$newsletter_content.'
	<br><br>
	Best regards<br><br>
	<strong>The I Want A Tradesman team</strong><br><br>
	<a href="http://www.iwantatradesman.co.uk" style="color: #666666;">www.iwantatradesman.co.uk</a></td></tr>
	<tr><td height="20"></td></tr>
	<tr><td style="color: #666666; font-size: 11px; font-family: arial, sans-serif;" align="left">You have received this email because you have requested to receive updates and news from <a href="http://www.iwantatradesman.co.uk" style="color: #666666;">iwantatradesman.co.uk</a>.<br><a href="http://www.iwantatradesman.co.uk/notification-settings" style="color: #666666;">Click here to unsubscribe.</a></td></tr>
	<tr><td height="20"></td></tr>
	<tr><td style="color: #666666; font-size: 11px; font-family: arial, sans-serif;" align="left">This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited.</td></tr>
	<tr><td height="20"></td></tr>
	</table>

</body>
</html>';
	

	$mail = new PHPMailer();
	$mail->IsSMTP(); // telling the class to use SMTP
	$mail->isHTML(true);
	$mail->Host = "localhost"; // SMTP server
	$mail->From = $settings['setting_value'];
	$mail->FromName = "I Want A Tradesman";

	$mail->AddAddress($rs['email']);

	$mail->Subject = "Job alerts from IWantATradesman";
	$mail->Body = $html_body;

	$mail->Send();
	$mail->ClearAddresses();
	
	$i++;
	
	}


}


echo $i.' sent';

?>